<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class M_laporan extends CI_Model{
	
	function laporan_pq(){
		$sql = "
			SELECT
			t_jadwalhistory.jadwalhistory_id as jadwalhistory_id,
			t_jadwalhistory.m_jadwal_id,
			t_jadwalhistory.m_cabang_id,
			t_jadwalhistory.jadwalhistory_koordinator as jadwal_koordinator,
			t_jadwalhistory.jadwalhistory_namaproyek as jadwal_namaproyek,
			t_jadwalhistory.jadwalhistory_alias as jadwal_alias,
			t_jadwalhistory.m_owner_id,
			t_jadwalhistory.jadwalhistory_matauang as jadwal_matauang,
			t_jadwalhistory.jadwalhistory_nk as jadwal_nk,
			t_jadwalhistory.jadwalhistory_pagu as jadwal_pagu,
			t_jadwalhistory.jadwalhistory_flagundangan as jadwal_flagundangan,
			t_jadwalhistory.jadwalhistory_flagllg as jadwal_flagllg,
			t_jadwalhistory.jadwalhistory_flagcr as jadwal_flagcr,
			t_jadwalhistory.jadwalhistory_tglcr as jadwal_tglcr,
			t_jadwalhistory.jadwalhistory_flagmr as jadwal_flagmr,
			t_jadwalhistory.jadwalhistory_flagijin as jadwal_flagijin,
			t_jadwalhistory.jadwalhistory_flagboq as jadwal_flagboq,
			t_jadwalhistory.jadwalhistory_flaqrks as jadwal_flaqrks,
			t_jadwalhistory.jadwalhistory_flaggbr as jadwal_flaggbr,
			t_jadwalhistory.jadwalhistory_tglpq as jadwal_tglpq,
			t_jadwalhistory.jadwalhistory_remkontrak as jadwal_remkontrak,
			t_jadwalhistory.jadwalhistory_updatestatus as jadwal_updatestatus,
			t_jadwalhistory.jadwalhistory_lastupdate as jadwal_lastupdate,
			t_jadwalhistory.m_kondisi_id,
			t_jadwalhistory.jadwalhistory_nodoc as jadwal_nodoc
			FROM
			t_jadwalhistory
			UNION
			SELECT t_jadwal.jadwal_id,
				t_jadwal.m_jadwal_id,
				t_jadwal.m_cabang_id,
				t_jadwal.jadwal_koordinator,
				t_jadwal.jadwal_namaproyek,
				t_jadwal.jadwal_alias,
				t_jadwal.m_owner_id,
				t_jadwal.jadwal_matauang,
				t_jadwal.jadwal_nk,
				t_jadwal.jadwal_pagu,
				t_jadwal.jadwal_flagundangan,
				t_jadwal.jadwal_flagllg,
				t_jadwal.jadwal_flagcr,
				t_jadwal.jadwal_tglcr,
				t_jadwal.jadwal_flagmr,
				t_jadwal.jadwal_flagijin,
				t_jadwal.jadwal_flagboq,
				t_jadwal.jadwal_flaqrks,
				t_jadwal.jadwal_flaggbr,
				t_jadwal.jadwal_tglpq,
				t_jadwal.jadwal_remkontrak,
				t_jadwal.jadwal_updatestatus,
				t_jadwal.jadwal_lastupdate,
				t_jadwal.m_kondisi_id,
				t_jadwal.jadwal_nodoc
			FROM
				t_jadwal
			LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
			WHERE lower(kondisi_nama) = 'PQ'
		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	function laporan_detailjadwal(){
		$sql = "SELECT t_jadwal.*,m_kondisi.* ,m_cabang.*, picanggaran.pic_name as jadwal_anggaranpicname,
			 picexpert.pic_name as jadwal_expertpicname,
			 picvolume.pic_name as jadwal_volumepicname,
			 picharga.pic_name as jadwal_hargapicname,
			 picme.pic_name as jadwal_mepicname,
			 picmetode.pic_name as jadwal_metodepicname
			 FROM t_jadwal 
			LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
			LEFT JOIN m_cabang ON m_cabang_id = cabang_id
			LEFT JOIN m_pic as picanggaran ON jadwal_picanggaran = picanggaran.pic_id
			LEFT JOIN m_pic as picexpert ON jadwal_picexpert = picexpert.pic_id
			LEFT JOIN m_pic as picvolume ON jadwal_picvolume = picvolume.pic_id
			LEFT JOIN m_pic as picharga ON jadwal_picharga = picharga.pic_id
			LEFT JOIN m_pic as picme ON jadwal_picme = picme.pic_id
			LEFT JOIN m_pic as picmetode ON jadwal_picmetode = picmetode.pic_id
			WHERE lower(kondisi_nama) <> 'selesai'
			ORDER BY m_cabang_id
		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	function laporan_rekapjadwal(){
		$sql = "SELECT * FROM (
				SELECT * FROM (
				SELECT jadwal_picanggaran as pic, jadwal_id FROM 
				t_jadwal as picanggaran
				UNION
				SELECT jadwal_picmetode as pic, jadwal_id FROM 
				t_jadwal as picmetode
				UNION
				SELECT jadwal_picme as pic, jadwal_id FROM 
				t_jadwal as picme
				UNION
				SELECT jadwal_picharga as pic, jadwal_id FROM 
				t_jadwal as picharga
				UNION
				SELECT jadwal_picvolume as pic , jadwal_id FROM 
				t_jadwal as picvolume
				UNION
				SELECT jadwal_picexpert as pic, jadwal_id FROM 
				t_jadwal as picexpert
				) t_all group by  pic , jadwal_id
				ORDER BY pic )
				all_new LEFT JOIN t_jadwal
				ON all_new.jadwal_id  = t_jadwal.jadwal_id
				LEFT JOIN m_pic
				ON all_new.pic  = m_pic.pic_id
				LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
				WHERE lower(kondisi_nama) <> 'selesai'
				AND pic <> '-'
				ORDER BY pic ASC
		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	function laporan_pic(){
		$sql = "SELECT * FROM (
				SELECT * FROM (
				SELECT jadwal_picanggaran as pic, jadwal_id FROM 
				t_jadwal as picanggaran
				UNION
				SELECT jadwal_picmetode as pic, jadwal_id FROM 
				t_jadwal as picmetode
				UNION
				SELECT jadwal_picme as pic, jadwal_id FROM 
				t_jadwal as picme
				UNION
				SELECT jadwal_picharga as pic, jadwal_id FROM 
				t_jadwal as picharga
				UNION
				SELECT jadwal_picvolume as pic , jadwal_id FROM 
				t_jadwal as picvolume
				UNION
				SELECT jadwal_picexpert as pic, jadwal_id FROM 
				t_jadwal as picexpert
				) t_all group by  pic , jadwal_id
				ORDER BY pic )
				all_new LEFT JOIN t_jadwal
				ON all_new.jadwal_id  = t_jadwal.jadwal_id
				LEFT JOIN m_pic
				ON all_new.pic  = m_pic.pic_id
				LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
				WHERE lower(kondisi_nama) <> 'selesai'
				AND pic <> '-'
				ORDER BY pic ASC
		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	function laporan_hasil($data){
		extract($data);
		$sql = "SELECT if(jadwal_tglpemasukan='1970-01-01', jadwal_tglpemasukan2, jadwal_tglpemasukan) as tanggal,
				t_jadwal.* , m_kondisi.*, m_owner.*, m_cabang.*
				FROM 
				t_jadwal 
				LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
				LEFT JOIN m_owner ON m_owner_id = owner_id
				LEFT JOIN m_cabang ON m_cabang_id = cabang_id
				WHERE 
				m_cabang_id = '".mysql_real_escape_string($cabang)."'
				AND jadwal_nolapor <> 'Y'
				AND (YEAR(jadwal_tglpemasukan) = '".mysql_real_escape_string($tahun)."'
				OR YEAR(jadwal_tglpemasukan2) = '".mysql_real_escape_string($tahun)."')
				ORDER BY owner_jenis, 1 ASC
		";
		$this->firephp->log($sql );
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	
	function laporan_masterlist($data){
		extract($data);
		$sql = "SELECT * FROM 
				t_jadwal 
				LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
				LEFT JOIN m_owner ON m_owner_id = owner_id
				LEFT JOIN m_cabang ON m_cabang_id = cabang_id
					LEFT OUTER JOIN t_revisi ON t_jadwal_id = jadwal_id
				WHERE m_cabang_id = '".mysql_real_escape_string($cabang)."'
				AND YEAR(jadwal_tglpemasukan) = '".mysql_real_escape_string($tahun)."'
				ORDER BY jadwal_nodoc ASC
		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	
	function laporan_tenderpekan($data){
		extract($data);
		$sql = "SELECT * FROM 
				t_jadwal 
				LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
				LEFT JOIN m_cabang ON m_cabang_id = cabang_id
				WHERE (YEAR(jadwal_tglpemasukan) = '".mysql_real_escape_string($tahun)."' OR 
                YEAR(jadwal_tglpemasukan2) = '".mysql_real_escape_string($tahun)."'  OR 
                YEAR(jadwal_tglaan) = '".mysql_real_escape_string($tahun)."' ) 
                AND
                (
                    (
                    (WEEK( jadwal_tglpemasukan) + 1 >= '".mysql_real_escape_string($pekanawal)."' )
                    AND
                    (WEEK( jadwal_tglpemasukan) + 1 <= '".mysql_real_escape_string($pekanakhir)."' )
                    )OR
                    (
                    (WEEK( jadwal_tglpemasukan2) + 1 >= '".mysql_real_escape_string($pekanawal)."' )
                    AND
                    (WEEK( jadwal_tglpemasukan2) + 1 <= '".mysql_real_escape_string($pekanakhir)."' )
                    )OR
                    (
                    (WEEK( jadwal_tglklarifikasi) + 1 >= '".mysql_real_escape_string($pekanawal)."' )
                    AND
                    (WEEK(  jadwal_tglklarifikasi) + 1 <= '".mysql_real_escape_string($pekanakhir)."' )
                    )OR
                    (
                    (WEEK( jadwal_tglaan) + 1 >= '".mysql_real_escape_string($pekanawal)."' )
                    AND
                    (WEEK(  jadwal_tglaan) + 1 <= '".mysql_real_escape_string($pekanakhir)."' )
                    )
                )
                 AND kondisi_nama = 'PASCA'
				ORDER BY m_cabang_id ASC
		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
    
	function laporan_tenderpekan2($data){
		extract($data);
		$sql = "SELECT * FROM 
				t_jadwal 
				LEFT JOIN m_kondisi ON m_kondisi_id = kondisi_id
				LEFT JOIN m_cabang ON m_cabang_id = cabang_id
				WHERE YEAR(jadwal_tglpq) = '".mysql_real_escape_string($tahun)."' 
                AND
                (WEEK( jadwal_tglpq) + 1 >= '".mysql_real_escape_string($pekanawal)."' )
                AND
                (WEEK( jadwal_tglpq) + 1 <= '".mysql_real_escape_string($pekanakhir)."' )
                
                AND ( kondisi_nama = 'PQ' OR  kondisi_nama = 'EE' )
				ORDER BY jadwal_nodoc ASC
		";
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	
	function getTahun($laporan = null){
		switch ($laporan){
			case "hasil" : 
				$sql = "SELECT * FROM (SELECT IF(YEAR(jadwal_tglpemasukan) = 1970, '', YEAR(jadwal_tglpemasukan))  AS TAHUN  FROM 
					t_jadwal 
                    UNION
                    SELECT IF(YEAR(jadwal_tglpemasukan2) = 1970, '', YEAR(jadwal_tglpemasukan2))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglaan) = 1970, '', YEAR(jadwal_tglaan))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglklarifikasi) = 1970, '', YEAR(jadwal_tglklarifikasi))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglpq) = 1970, '', YEAR(jadwal_tglpq))  AS TAHUN  FROM 
					t_jadwal
                    ) s
					GROUP BY TAHUN";
				$query = $this->db->query($sql);
				$result = $query->result();
				$data = array("-");
				foreach($result as $row){
					$data[$row->TAHUN] = $row->TAHUN;
				}
				return $data ;
				break;
			case "masterlist" :
				$sql = "SELECT * FROM (SELECT IF(YEAR(jadwal_tglpemasukan) = 1970, '', YEAR(jadwal_tglpemasukan))  AS TAHUN  FROM 
					t_jadwal 
                    UNION
                    SELECT IF(YEAR(jadwal_tglpemasukan2) = 1970, '', YEAR(jadwal_tglpemasukan2))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglaan) = 1970, '', YEAR(jadwal_tglaan))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglklarifikasi) = 1970, '', YEAR(jadwal_tglklarifikasi))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglpq) = 1970, '', YEAR(jadwal_tglpq))  AS TAHUN  FROM 
					t_jadwal
                    ) s
					GROUP BY TAHUN";
				$query = $this->db->query($sql);
				$result = $query->result();
				$data = array("-");
				foreach($result as $row){
					if ( $row->TAHUN != '')
						$data[$row->TAHUN] = $row->TAHUN;
				}
				return $data ;
				break;
			case "tenderpekan" :
				$sql = "SELECT * FROM (SELECT IF(YEAR(jadwal_tglpemasukan) = 1970, '', YEAR(jadwal_tglpemasukan))  AS TAHUN  FROM 
					t_jadwal 
                    UNION
                    SELECT IF(YEAR(jadwal_tglpemasukan2) = 1970, '', YEAR(jadwal_tglpemasukan2))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglaan) = 1970, '', YEAR(jadwal_tglaan))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglklarifikasi) = 1970, '', YEAR(jadwal_tglklarifikasi))  AS TAHUN  FROM 
					t_jadwal
                    UNION
                    SELECT IF(YEAR(jadwal_tglpq) = 1970, '', YEAR(jadwal_tglpq))  AS TAHUN  FROM 
					t_jadwal
                    ) s
					GROUP BY TAHUN";
				$query = $this->db->query($sql);
				$result = $query->result();
				$data = array("-");
				foreach($result as $row){
					if ( $row->TAHUN != '')
						$data[$row->TAHUN] = $row->TAHUN;
				}
				return $data ;
				break;
			default : 
				return  array("-");
		}
		
	}
}